package com.group3.customer.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.group3.customer.dao.ICustomerDao;
import com.group3.customer.util.DBUtil;
import com.group3.entity.Dish;
import com.group3.entity.Merchant;

public class CustomerDaoImpl implements ICustomerDao {

	public List<Merchant> queryByDishTypeId(String dtId) {
		// TODO Auto-generated method stub
		List<Merchant> list = new ArrayList<Merchant>();
		String sql = "select * from m_dish where pid=?";
		try {
			Connection conn = DBUtil.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, dtId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// String mId=rs.getString("m_id");
				// String dName=rs.getString("m_name");
				// String dImageURL=rs.getString("m_imageURL");
				// String address=rs.getString("m_address");

			}
			DBUtil.free(conn, ps, rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	// 1,
	public Merchant queryMerchant(String mId) {
		String sql = "select * from merchant where m_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			rs = ps.executeQuery();
			if (rs.next()) {
				String mid = rs.getString("m_id");
				String mName = rs.getString("m_name");
				String password = rs.getString("m_password");
				String address = rs.getString("m_address");
				String image = rs.getString("m_imageurl");
				String idCard = rs.getString("m_idcardurl");
				return new Merchant(mid, mName, password, address, image,
						idCard);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.free(conn, ps, rs);
		}
		return null;
	}

	public List<Dish> queryDishByDishId(String dtId) {
		String sql = "select * from dish where pid=?";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, dtId);
			rs = ps.executeQuery();
			while (rs.next()) {
				String dId = rs.getString("did");
				String dName = rs.getString("name");
				double dPrice = Double.parseDouble(rs.getString("price"));
				String dImageURL = rs.getString("imageURL");
				String mId = rs.getString("mid");
				String pId = rs.getString("pid");
				list.add(new Dish(dId, dName, dPrice, dImageURL, mId, pId));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally {
			DBUtil.free(conn, ps, rs);
		}
		return list;
	}

	public List<Dish> queryAllDish(String dtId) {
		String sql = "select * from DISH where pid=?";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, dtId);
			rs = ps.executeQuery();
			while (rs.next()) {
				String dId = rs.getString("did");
				String dName = rs.getString("name");
				double dPrice = Double.parseDouble(rs.getString("price"));
				String dImageURL = rs.getString("imageURL");
				String mId = rs.getString("mid");
				String pId = rs.getString("pid");
				list.add(new Dish(dId, dName, dPrice, dImageURL, mId, pId));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.free(conn, ps, rs);
		}

		return list;
	}

	// 2,
	public List<Dish> queryAllDishByMid(String mid) {
		String sql = "select * from dish where mid=?";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, mid);
			rs = ps.executeQuery();
			while (rs.next()) {
				String dId = rs.getString("did");
				String dName = rs.getString("name");
				double dPrice = Double.parseDouble(rs.getString("price"));
				String dImageURL = rs.getString("imageURL");
				String mId = rs.getString("mid");
				String pId = rs.getString("pid");
				list.add(new Dish(dId, dName, dPrice, dImageURL, mId, pId));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.free(conn, ps, rs);
		}
		return list;
	}

	// 3,
	public List<Dish> queryDishByMidDpid(String mId, String dtId) {
		List<Dish> list = new ArrayList<Dish>();
		String sql = "select * from dish where mid=? and pid=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ps.setString(2, dtId);
			rs = ps.executeQuery();
			while (rs.next()) {
				String dId = rs.getString("did");
				String dName = rs.getString("name");
				double dPrice = Double.parseDouble(rs.getString("price"));
				String dImageURL = rs.getString("imageURL");
				String mid = rs.getString("mid");
				String pId = rs.getString("pid");
				list.add(new Dish(dId, dName, dPrice, dImageURL, mid, pId));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.free(conn, ps, rs);
		}

		return list;
	}

	public Merchant queryMarcherByType(String mid, String tdid) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			String sql = "select t1.m_id, t1.m_name, t1.m_password, t1.m_address, t1.m_imageurl, t1.m_idcardurl from merchant t1 inner join dish t2 on t1.m_id = t2.mid where t1.m_id = ? and t2.pid = ? ";
			ps = connection.prepareStatement(sql);
			ps.setString(1, mid);
			ps.setString(2, tdid);
			rs = ps.executeQuery();
			Merchant merchant = null;
			if (rs.next()) {
				merchant = new Merchant(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5),
						rs.getString(6));
				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(5));
				System.out.println(rs.getString(6));

			}
			return merchant;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.free(connection, ps, rs);
		}
		return null;
	}

	public Dish queryDish(String dId) {
		String sql = "select * from dish where did = ?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, dId);
			rs = ps.executeQuery();
			if(rs.next()) {
				Dish dish = new Dish(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getString(5), rs.getString(6));
				return dish;
			}
			DBUtil.free(connection, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
